    #!/bin/perl
package data;
    use strict;
    use DBI;
    use utf8;

	#这一句，可以避免出现由于变量只使用一次出现的 use only once 警告 
	no warnings('once');  
	#包含配置文件
	require '../conf/config.pl';
    
    our %db_conf = %conf::db_conf ; 
    my $db_host = $db_conf{'db_host'};
    my $db_user = $db_conf{'db_user'};
    my $db_pwd = $db_conf{'db_pwd'};

    # Connect to target DB
    my $dbh = DBI->connect("$db_host","$db_user","$db_pwd", {'RaiseError' => 1,'mysql_enable_utf8'=>1});

	my $temp="";

    #my $sqr = $dbh->prepare("SELECT t.customer_id as customer,t.title as subject,u.first_name as engineer,t.create_time as time  FROM ticket t,users u where t.user_id=u.id and t.queue_id<>3 order by t.create_time desc ");

    my $contents="<style type='text/css'>div{border:0px solid #CCC;} div.h{float:left;width:80px;margin:auto;overflow-x:hidden;} body {background-color:#D5D5D5} table.header {font-size:13pt;background-color:#F3F3F3; width:95%; height:8;border-top: 2px solid #FFFFFF; border-left: 2px solid #FFFFFF; border-right: 2px solid #8995FA; border-bottom: 2px solid #8995FA; } table.rate {font-size:16pt;background-color:#F3F3F3;width:95% } table.maincontent {font-size:11pt;background-color:#F3F3F3;width:95%;border-collapse:collapse;  } table.maincontent td{border:solid #000 1px;} table.content {font-size:10pt;background-color:#F3F3F3;width:95% }
 table.contentG {font-size:11pt;background-color:#FFCCFF;width:95% } table.contentN {font-size:10pt;background-color:#CCCCCC;width:95% } table.footer {font-size:10pt; background-color:#669900;width:95%} P {text-align:left;color:red} .A {width:100%;height:10;text-align:center} .B {text-align:center } .C {color:#AC0000} .D{color:#339933} .E {width:150} .F {font-weight:bold,color:#FF0000} a:visited{color:blue;text-decoration:none} a:link{text-decoration:none} .H {font-size:11pt;background-color:#669900;}</style>
<html><body><table class=header align='center'><tr><td class=A><a class=F>和黄商贸报障中心 - 日报表</a></td></tr></table>";

    $contents.="<table class=content align='center'>";

    # 获取本周支持工程师报障处理数量
    my $sqr = $dbh->prepare("SELECT a.first_name as name,count(1) as total FROM users a,ticket b where a.id=b.user_id and b.queue_id not in (3,12,13,9,10) and (b.create_time>subdate(curdate(),date_format(curdate(),'%w')+1) and b.create_time<subdate(curdate(),date_format(curdate(),'%w')-6)) group by a.first_name order by count(1) desc");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>本周报障处理情况【上周六～本周五】</th></tr>";
    $contents.="<tr><th>支持工程师</th><th>本周处理请求</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B>$ref->{'name'}</td><td class=B>$ref->{'total'}</td></tr>";

    }
    $contents.="</table>";

    # 获取本周表扬
    $contents.="<table class=contentG align='center'>";
    $sqr = $dbh->prepare("SELECT u.id as id,concat(u.first_name,' ',u.last_name) as engineer,count(1) as amount  FROM ticket t left join users u  on t.user_id=u.id left join customer_user cu on cu.customer_id=t.customer_user_id  where  t.queue_id=12 and ((t.create_time>subdate(curdate(),date_format(curdate(),'%w')+1) and t.create_time<subdate(curdate(),date_format(curdate(),'%w')-6))) and t.ticket_state_id in (2,3,9) group by u.id order by amount desc");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=3  class=H>本周受赞扬人员</th></tr>";
    $contents.="<tr><th width=20%>被表扬的支持工程师</th><th width=10%>被表扬次数</th><th width=80%>详情</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {
		
		my $sub1contents="";
		my $moresqr = $dbh->prepare("SELECT t.tn as no,ifnull(cu.city,'') as city,IFNULL(concat(cu.first_name,' ',cu.last_name),t.customer_id) as customer,t.title as subject,u.first_name as engineer,time(t.create_time) as time  FROM ticket t left join users u  on t.user_id=u.id left join customer_user cu on cu.customer_id=t.customer_user_id  where  t.queue_id=12 and ((t.create_time>subdate(curdate(),date_format(curdate(),'%w')+1) and t.create_time<subdate(curdate(),date_format(curdate(),'%w')-6))) and t.ticket_state_id in (2,3,9) and u.id=$ref->{id} order by t.create_time desc ");
		$moresqr->execute();
		while(my $moreref = $moresqr->fetchrow_hashref()) {
				my $subsqr = $dbh->prepare("select a.a_body as body from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$moreref->{no}' order by a.change_time");
				my $subcontents="";
				$subsqr->execute();
				while(my $subref = $subsqr->fetchrow_hashref()) {
					$subcontents.="$subref->{body}";
				}
					$subcontents=~ s/<snip>/ /g;
					$subcontents=~ s/</ /g;
					$subcontents=~ s/>/ /g;
					$subcontents=~ s/"/ /g;
					$sub1contents.="<a title=\"$subcontents\" style='cursor:hand'> [$moreref->{customer}] </a> &nbsp;&nbsp; ";
					
		}
		
		
	#print $subcontents;
	$contents.="<tr><td > <strong>$ref->{engineer}</strong> </td><td class=B><strong> $ref->{amount}</strong> </td><td> $sub1contents </td></tr> ";
    }

    $contents.="</table>";


    # 获取本周投诉
    $contents.="<table class=contentN align='center'>";
    $sqr = $dbh->prepare("SELECT t.tn as no,IFNULL(cu.first_name,t.customer_id) as customer,t.title as subject,u.first_name as engineer,time(t.create_time) as time  FROM ticket t left join users u  on t.user_id=u.id left join customer_user cu on cu.customer_id=t.customer_user_id  where  t.queue_id=13 and ((t.create_time>subdate(curdate(),date_format(curdate(),'%w')+1) and t.create_time<subdate(curdate(),date_format(curdate(),'%w')-6))) and t.ticket_state_id in (2,3,9) order by t.create_time desc  ");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=5  class=H>本周被投诉人员</th></tr>";
    $contents.="<tr><th>单号</th><th>申请人员</th><th>主题</th><th>被投诉的支持工程师</th><th> </th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	my $subsqr = $dbh->prepare("select a.a_body as body from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' order by a.change_time");

	$subsqr->execute();
	my $subcontents="";
	while(my $subref = $subsqr->fetchrow_hashref()) {
		$subcontents.="$subref->{body}";
	}
		$subcontents=~ s/<snip>/ /g;
		$subcontents=~ s/</ /g;
		$subcontents=~ s/>/ /g;
		$subcontents=~ s/"/ /g;

	$contents.="<tr><td> $ref->{no} </td><td class=B> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td title=\"$subcontents\" style='cursor:hand'> 更多详情[鼠标停留] </td></tr> ";
    }

    $contents.="</table>";



    # 获取未处理申请
    # 排除【作废】【表扬】【投诉】
    $contents.="<table class=content align='center'>";
    $sqr = $dbh->prepare("SELECT t.tn as no,tt.name as type,IFNULL(cu.first_name,t.customer_id) as customer,t.title as subject,u.first_name as engineer,timestampdiff(hour,t.create_time,now()) as time  FROM (ticket t left join customer_user cu on cu.customer_id=t.customer_user_id) left join users u on u.id=t.user_id left join ticket_type tt on t.type_id=tt.id where   t.queue_id not in (3,12,13) and t.ticket_state_id not in (2,3,9) order by type desc,t.create_time ");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=6  class=H>未关闭报障处理情况</th></tr>";
    $contents.="<tr><th width=10%>报障类型</th><th width=5%>申请人员</th><th width=47%>主题</th><th  width=8%>支持工程师</th><th width=10%>持续时间(小时）</th><th width=10%>详情[鼠标停留]</th></tr>";
    $temp="";
    while(my $ref = $sqr->fetchrow_hashref()) {

	my $subsqr = $dbh->prepare("select a.a_body as body from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' order by a.change_time");

	$subsqr->execute();
	my $subcontents="";
	while(my $subref = $subsqr->fetchrow_hashref()) {
		$subcontents.="$subref->{body}";

	}
		$subcontents=~ s/<snip>/ /g;
		$subcontents=~ s/</ /g;
		$subcontents=~ s/>/ /g;
		$subcontents=~ s/"/ /g;

	if ($temp eq $ref->{type}) {
		$contents.="<tr><td> </td><td> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td><a title=\"$subcontents\" style='cursor:hand'>$ref->{no}</a></td></tr> ";
	}
	else {
		$contents.="<tr><td> $ref->{type} </td><td> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td><a title=\"$subcontents\" style='cursor:hand'>$ref->{no}</a></td></tr> ";
		$temp = $ref->{type};
	} 

	#$contents.="<tr><td> $ref->{no} </td><td> $ref->{type} </td><td class=B> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td title=\"$subcontents\" style='cursor:hand'> 详情[鼠标停留] </td></tr> ";
    }

    $contents.="</table>";

    
    # 获取今天处理申请
    $contents.="<table class=content align='center'>";
    my $multisql = <<"___MULTISQL___";
SELECT t.tn as no,tt.name as type,IFNULL(cu.first_name,t.customer_user_id) as customer,t.title as subject,u.first_name as engineer,t.create_time as time  
FROM (ticket t left join customer_user cu on cu.customer_id=t.customer_user_id) left join users u on u.id=t.user_id left join ticket_type tt on t.type_id=tt.id  
where  t.queue_id not in (3,12,13,9,10) and (t.change_time>subdate(curdate(),0) and t.change_time<subdate(curdate(),-1)) and t.ticket_state_id in (2,3,9)
order by type desc,t.change_time 
___MULTISQL___
    $sqr = $dbh->prepare("$multisql");
    $sqr->execute();

    $contents.="<tr><th COLSPAN=6  class=H>今天关闭的报障情况</th></tr>";
    $contents.="<tr><th width=10%>报障类型</th><th width=5%>申请人员</th><th width=47%>主题</th><th  width=8%>支持工程师</th><th width=10%>持续时间(小时）</th><th width=10%>详情[鼠标停留]</th></tr>";
    
	$temp="";
    
    while(my $ref = $sqr->fetchrow_hashref()) {

	my $subsqr = $dbh->prepare("select a.a_body as body from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' order by a.change_time");

	$subsqr->execute();
	my $subcontents="";
	while(my $subref = $subsqr->fetchrow_hashref()) {
		$subcontents.="$subref->{body}";
	}
		$subcontents=~ s/<snip>/ /g;
		$subcontents=~ s/</ /g;
		$subcontents=~ s/>/ /g;
		$subcontents=~ s/"/ /g;

	if ($temp eq $ref->{type}) {
		$contents.="<tr><td></td><td> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td><a title=\"$subcontents\" style='cursor:hand'>$ref->{no}</a></td></tr> ";
	}
	else {
		$contents.="<tr><td> $ref->{type} </td><td> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td><a title=\"$subcontents\" style='cursor:hand'>$ref->{no}</a></td></tr> ";
		$temp = $ref->{type};
	} 
    }

    $contents.="</table>";


    # 获取今天入职/离职申请
    $contents.="<table class=content align='center'>";
    $multisql = <<"___MULTISQL___";
SELECT t.tn as no,IFNULL(cu.first_name,t.customer_user_id) as customer,t.title as subject,u.first_name as engineer,time(t.create_time) as time  FROM (ticket t left join customer_user cu on cu.customer_id=t.customer_user_id) left join users u on u.id=t.user_id  where  t.queue_id in (9,10) and (t.create_time>subdate(curdate(),0) and t.create_time<subdate(curdate(),-1)) and t.ticket_state_id in (2,3,9) 

___MULTISQL___
    $sqr = $dbh->prepare("$multisql");

    $sqr->execute();
    
    $contents.="<tr><th COLSPAN=6  class=H>今天入职/离职处理情况</th></tr>";
    $contents.="<tr><th>报障单号</th><th>申请人员</th><th>主题</th><th>支持工程师</th><th>申请时间</th><th> </th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	my $subsqr = $dbh->prepare("select a.a_body as body from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' order by a.change_time");

	$subsqr->execute();
	my $subcontents="";
	while(my $subref = $subsqr->fetchrow_hashref()) {
		$subcontents.="$subref->{body}";
	}
		$subcontents=~ s/<snip>/ /g;
		$subcontents=~ s/</ /g;
		$subcontents=~ s/>/ /g;
		$subcontents=~ s/"/ /g;
	$contents.="<tr><td> $ref->{no} </td><td class=B> $ref->{customer} </td><td> $ref->{subject} </td><td class=B> $ref->{engineer} </td><td class=B> $ref->{time} </td><td title=\"$subcontents\" style='cursor:hand'> 详情[鼠标停留] </td></tr> ";
    }

    $contents.="</table>";


    # 获取IT助手邮件地址

    $sqr = $dbh->prepare("select u.first_name as name,up.preferences_value as email from users u,user_preferences up where u.id=up.user_id and up.preferences_key='UserEmail' and u.first_name like 'IT%'");

    $sqr->execute();

    my $to = 'EBIS <toebis@hwccl.com>';

     while(my $ref = $sqr->fetchrow_hashref()) {

	$to.=",$ref->{name} <$ref->{email}>";
    }


    $dbh->disconnect();

    

    my $footer = <<"___FOOT___";
<table class=content align='center'>
<tr></tr><tr></tr><tr></tr><tr><td class=D> 和黄商贸办公网站入口 : <a href="my.hwccl.com">my.hwccl.com</a></td></tr>
<tr></tr><tr><td class=D>计算机/网络故障处理方法： 1.联系报障中心：<a href="mailto:it\@hwccl.com">it\@hwccl.com</a> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.IMO-在搜索栏输入： it </td></tr>
<tr><td> </td></tr><tr></tr><tr></tr>

<tr><td class=D>
IT技术服务工程师：<br>
Suan: 020-86266855/6833-291<br>
Landy: 020-86266855/6833-324<br>
Marco:020-86266855/6833-326<br>
Homy: 020-86266855/6833-235
</td></tr></table>
<table class=footer align='center'><tr><td class=B>版权所有：<font color=#FF0000>和记黄埔（中国）商贸有限公司</font></td></tr></table></body></html>
___FOOT___

    $contents.=$footer;
    $to='Meanson <meansonw@hwccl.com>';
    #print $to;

our %db = (
html => $contents,
to => $to
);
